package unilearn.da;

import java.sql.*;
import java.util.Vector;
import javax.naming.*;

import unilearn.domain.Extension;

/**
 * @author Yin Zhang, 5393558
 * @author Ben Morrall, 4158768
 * @version 0.1, 10 May 2008, initial creation
 * @version 0.2, 14 May 2008, Refactor code, blm
 */
public class DAExtension extends DABase
{
	public static Extension deleteExtension()
	{
		throw new Error("deleteExtension not implemented"); // TODO
	}

	public static Extension getExtensionByStudentAndAssignment(long assignmentID,
			String studentID) throws SQLException, NamingException
	{
		Extension extension = null;
		PreparedStatement ps = null;
		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("SELECT * FROM extension WHERE assignment_id = ? AND student_id = ?");
			ps.setLong(1, assignmentID);
			ps.setString(2, studentID);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
			{
				extension = new Extension(rs.getLong("assignment_id"),
						rs.getString("student_id"));
				extension.setExtensionName(rs.getString("name"));
				extension.setExtensionDescription(rs.getString("description"));
				extension.setExtensionGrantedDate(rs.getTimestamp("granted_date"));
				extension.setExtensionFinishedDate(rs.getTimestamp("finished_date"));
				extension.setExtensionGrantedBy(rs.getString("granted_by"));
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return extension;
	}

	public static Vector<Extension> getExtensionsByAssignment(long assignmentID)
		throws SQLException, NamingException
	{
		Vector<Extension> extensions = new Vector<Extension>();
		PreparedStatement ps = null;
		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("SELECT * FROM extension WHERE assignment_id = ?");
			ps.setLong(1, assignmentID);

			ResultSet rs = ps.executeQuery();

			while (rs.next())
			{
				Extension extension = new Extension();
				extension.setAssignmentID(rs.getLong("assignment_id"));
				extension.setStudentID(rs.getString("student_id"));
				extension.setExtensionName(rs.getString("name"));
				extension.setExtensionDescription(rs.getString("description"));
				extension.setExtensionGrantedDate(rs.getTimestamp("granted_date"));
				extension.setExtensionFinishedDate(rs.getTimestamp("finished_date"));
				extension.setExtensionGrantedBy(rs.getString("granted_by"));
				extensions.add(extension);
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return extensions;
	}

	public static Vector<Extension> getExtensionsByStaffID(String staffID)
		throws SQLException, NamingException
	{
		Vector<Extension> extensions = new Vector<Extension>();
		PreparedStatement ps = null;
		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("SELECT * FROM extension WHERE granted_by = ?");
			ps.setString(1, staffID);

			ResultSet rs = ps.executeQuery();

			while (rs.next())
			{
				Extension extension = new Extension();
				extension.setAssignmentID(rs.getLong("assignment_id"));
				extension.setStudentID(rs.getString("student_id"));
				extension.setExtensionName(rs.getString("name"));
				extension.setExtensionDescription(rs.getString("description"));
				extension.setExtensionGrantedDate(rs.getTimestamp("granted_date"));
				extension.setExtensionFinishedDate(rs.getTimestamp("finished_date"));
				extension.setExtensionGrantedBy(rs.getString("granted_by"));
				extensions.add(extension);
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return extensions;
	}

	public static Vector<Extension> getExtensionsByStudentID(String studentID)
		throws SQLException, NamingException
	{
		Vector<Extension> extensions = new Vector<Extension>();
		PreparedStatement ps = null;
		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("SELECT * FROM extension WHERE student_id = ?");
			ps.setString(1, studentID);

			ResultSet rs = ps.executeQuery();

			while (rs.next())
			{
				Extension extension = new Extension();
				extension.setAssignmentID(rs.getLong("assignment_id"));
				extension.setStudentID(rs.getString("student_id"));
				extension.setExtensionName(rs.getString("name"));
				extension.setExtensionDescription(rs.getString("description"));
				extension.setExtensionGrantedDate(rs.getTimestamp("granted_date"));
				extension.setExtensionFinishedDate(rs.getTimestamp("finished_date"));
				extension.setExtensionGrantedBy(rs.getString("granted_by"));
				extensions.add(extension);
			}
		}
		finally
		{
			if (ps != null) ps.close();
		}

		return extensions;
	}

	public static void insertExtension(Extension extension) throws SQLException,
		NamingException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("INSERT INTO extension "
					+ "(assignment_id, student_id, name, "
					+ "description, granted_date, finished_date, granted_by) "
					+ "VALUES (?,?,?,?,?,?,?)");

			ps.setLong(1, extension.getAssignmentID());
			ps.setString(2, extension.getStudentID());
			ps.setString(3, extension.getExtensionName());
			ps.setString(4, extension.getExtensionDescription());
			ps.setTimestamp(5, extension.getExtensionGrantedDate());
			ps.setTimestamp(6, extension.getExtensionFinishedDate());
			ps.setString(7, extension.getExtensionGrantedBy());

			ps.execute();
		}
		finally
		{
			if (ps != null) ps.close();
		}
	}

	public static void updateExtension(long assignmentID, String studentID,
			Extension extension) throws SQLException, NamingException
	{
		PreparedStatement ps = null;

		try
		{
			Connection conn = getConn();

			ps = conn.prepareStatement("UPDATE extension SET "
					+ " name = ?, "
					+ "description = ?, granted_date = ?, finished_date = ?, "
					+ "granted_by = ? WHERE assignment_id = ? AND student_id = ?");

			ps.setString(1, extension.getExtensionName());
			ps.setString(2, extension.getExtensionDescription());
			ps.setTimestamp(3, extension.getExtensionGrantedDate());
			ps.setTimestamp(4, extension.getExtensionFinishedDate());
			ps.setString(5, extension.getExtensionGrantedBy());
			ps.setLong(6, assignmentID);
			ps.setString(7, studentID);

			ps.execute();

		}
		finally
		{
			if (ps != null) ps.close();
		}
	}

	/**
	 * Prevent Instances of DAExtension
	 */
	private DAExtension()
	{
	}
}